﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Windows.Forms;
using System.Data;
using SYDZ.WMS.DBUtility;

namespace ElectronicWMSA.Comm.ClassFiles
{
    class ExPut
    {
        /// <summary>
        /// Nopi导出，不需要模板
        /// </summary>
        /// <param name="mydgv">datatabale带标题</param>
        public static void GetNopiExcel(DataTable mydgv)
        {
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.Filter = "xls(*.xls)|*.xls";
            saveFileDialog1.FilterIndex = 0;
            saveFileDialog1.RestoreDirectory = true;
            saveFileDialog1.CreatePrompt = true;
            saveFileDialog1.Title = "导出xls文件到 ";
            saveFileDialog1.ShowDialog();
            string sSaveFullPath = saveFileDialog1.FileName;//导出全路径
            if (sSaveFullPath != "")
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                try
                {
                    ISheet sheet = workbook.CreateSheet("Sheet1");
                    ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                    HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                    headerfont.Boldweight = (short)FontBoldWeight.Bold;
                    HeadercellStyle.SetFont(headerfont);

                    int icolIndex = 0;
                    IRow headerRow = sheet.CreateRow(0);
                    foreach (DataColumn item in mydgv.Columns)
                    {
                        ICell cell = headerRow.CreateCell(icolIndex);
                        cell.SetCellValue(item.ColumnName);
                        cell.CellStyle = HeadercellStyle;
                        icolIndex++;
                    }

                    ICellStyle cellStyle = workbook.CreateCellStyle();

                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                    cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;


                    NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                    cellfont.Boldweight = (short)FontBoldWeight.Normal;
                    cellStyle.SetFont(cellfont);

                    int iRowIndex = 1;
                    int iCellIndex = 0;
                    foreach (DataRow Rowitem in mydgv.Rows)
                    {
                        IRow DataRow = sheet.CreateRow(iRowIndex);
                        foreach (DataColumn Colitem in mydgv.Columns)
                        {

                            ICell cell = DataRow.CreateCell(iCellIndex);
                            cell.SetCellValue(Rowitem[Colitem].ToString());
                            cell.CellStyle = cellStyle;
                            iCellIndex++;
                        }
                        iCellIndex = 0;
                        iRowIndex++;
                    }

                    for (int i = 0; i < icolIndex; i++)
                    {
                        sheet.AutoSizeColumn(i);
                    }
                    FileStream file = new FileStream(sSaveFullPath, FileMode.OpenOrCreate);
                    workbook.Write(file);
                    file.Flush();
                    file.Close();
                    MessageBox.Show("导出成功");

                }
                catch (Exception ex)
                {
                    MessageBox.Show("导出失败！" + ex.ToString());
                }
                finally
                {
                    workbook = null;
                }
            }
        }

        // Nopi导出，不需要模板
        /// <summary>
        /// 
        /// </summary>
        /// <param name="Sql"></param>
        /// <param name="sqltable"></param>
        /// <param name="where"></param>
        public static void GetNopiExcel(string Sql, string sqltable, string where)
        {
            DataTable mydgv = new DataTable();
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("  select  ");

                if (!string.IsNullOrEmpty(Sql))
                {
                    sb.Append(Sql);
                }
                else
                {
                    sb.Append(" * ");
                }
                sb.Append("   from   ");
                sb.Append(sqltable);
                if (!string.IsNullOrEmpty(where))
                {
                    sb.Append(" where " + where + "  ");

                }
                mydgv = DbHelperSQL.ExecuteDataTable(sb.ToString());

            }
            catch
            {
                MessageBox.Show("查询出错");
            }
            //打开窗体获取到路径
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.Filter = "xls(*.xls)|*.xls";
            saveFileDialog1.FilterIndex = 0;
            saveFileDialog1.RestoreDirectory = true;
            saveFileDialog1.CreatePrompt = true;
            saveFileDialog1.Title = "导出xls文件到 ";

            saveFileDialog1.ShowDialog();

            string sSaveFullPath = saveFileDialog1.FileName;//导出全路径
            HSSFWorkbook workbook = new HSSFWorkbook();
            try
            {


                //设置样式

                ISheet sheet = workbook.CreateSheet("Sheet1");

                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);


                //用首列 column name 作为列名
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                foreach (DataColumn item in mydgv.Columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(item.ColumnName);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;


                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                //建立内容行
                int iRowIndex = 1;
                int iCellIndex = 0;
                foreach (DataRow Rowitem in mydgv.Rows)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    foreach (DataColumn Colitem in mydgv.Columns)
                    {

                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.SetCellValue(Rowitem[Colitem].ToString());
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //写Excel
                FileStream file = new FileStream(sSaveFullPath, FileMode.OpenOrCreate);
                workbook.Write(file);
                file.Flush();
                file.Close();
                MessageBox.Show("导出成功");

            }
            catch (Exception)
            {
                // MessageBox.Show("到处失败,错误："+ex);
            }
            finally
            {
                workbook = null;
            }
        }
    }
}
